package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBCustomer implements IFDBCustomer {
	private Connection con;
	private PreparedStatement pStmtSelect;
	public DBCustomer()
	{
		con = DbConnection.getInstance().getDBcon();
		pStmtSelect = null;
	}
	
	public Customer findCustomer(int customerId, boolean retriveAssociation)
	{
		Customer custObj = new Customer();
		custObj = findWithPreQuery(customerId, retriveAssociation);
		return custObj;
	}
	
	public Customer findWithPreQuery(int customerId, boolean retriveAssociation)
    {   System.out.println("Find with preCompiled");
        if(pStmtSelect == null){
           pStmtSelect =  getPreparedStmt();          
        }
        Customer cObj = null;
        ResultSet results;
        try{
       
        pStmtSelect.setInt(1,customerId);
        results = pStmtSelect.executeQuery();
        if(results.next()){
             cObj = buildCustomer(results);	
        }
        }
        catch(Exception e){
            e.getMessage();
        }
        return cObj;
    }
	
	private PreparedStatement getPreparedStmt()
    {
       try{ 
        String query = "select name, customerid from customer" +
                       " where customerId = ?";
        System.out.println("Preparestatement " + query);
        pStmtSelect = con.prepareStatement(query);
        
       }
       catch(Exception e){
            e.getMessage();
        }
       return pStmtSelect;
    }
	
	
	
	
	@Override
	public ArrayList<Customer> getAllCustomer(boolean retriveAssociation) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Customer searchCustomerOnName(String attValue, boolean retriveAssociation) {
		String wClause = "name like '%" + attValue + "%'";
        System.out.println("Search Customer " + wClause);
        return singleWhere(wClause, retriveAssociation);
	}
	
	private Customer singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Customer custObj = new Customer();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 			custObj = buildCustomer(results);
                            //assocaition is to be build
                            stmt.close();
                            
			}
                        else{ //no employee was found
                        	custObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return custObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT name, customerid  FROM customer";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an employee object
	private Customer buildCustomer(ResultSet results)
      {   Customer custObj = new Customer();
          try{ // the columns from the table customer are used
        	  	custObj.setName(results.getString("name"));
                custObj.setCustomerId(results.getInt("customerID"));
                }
         catch(Exception e)
         {
             System.out.println("error in building the employee object");
         }
         return custObj;
      }

	public int insertCustomer(Customer cusObj, boolean retriveAssociation) {
		 int rc = -1;
	        PreparedStatement pstmt = null;
	        String insert = "insert into Customer(Name, Address, ZipCode, City, PhoneNo, CustomerId, CustomerType)" +
	                "values (?,?,?,?,?,?,?)";
	        System.out.println(insert);
	        try
	        {
	            pstmt = con.prepareStatement(insert);
	            pstmt.setString(1,cusObj.getName());
	            pstmt.setString(2,cusObj.getAddress());
	            pstmt.setInt(3,cusObj.getZipCode());   
	            pstmt.setString(4,cusObj.getCity());
	            pstmt.setInt(5,cusObj.getPhoneNo());
	            pstmt.setInt(6,cusObj.getCustomerId());
	            pstmt.setString(7,cusObj.getCustomerType());
	            rc = pstmt.executeUpdate();
	        }
	        catch(SQLException sqlE)
	        {    
	             System.out.println("FEJL");  
	             System.out.println(sqlE.getMessage());
	        }
	        catch(Exception e)
	        {
	            e.getMessage();
	        }
	        return rc;
	    }

	
	@Override
	public int update(Customer cust) {
		// TODO Auto-generated method stub
		return 0;
	}
	public int delete(Customer cust) {
		// TODO Auto-generated method stub
		return 0;
	}

	public int insertCustomer1(Customer cus, boolean b) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int deleteCustomer(int customerId) {
		// TODO Auto-generated method stub
		return 0;
	}

}
